package com.ty.dao.impl;

import com.ty.bean.Courier;
import com.ty.dao.BaseCourierDao;
import com.ty.util.DruidUtil;

import javax.swing.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 田野
 */
public class BaseCourierDaoImpl implements BaseCourierDao {
    private static final String DQL_CONSOLE = " SELECT COUNT(ID) DATA1_SIZE, COUNT(TO_DAYS(REGTIME)=TO_DAYS(NOW()) OR NULL) DATA1_DAY FROM ecourier ";
    // 用于查询数据库中的所有快递员信息
    private static final String DQL_FIND_ALL = " SELECT * FROM ecourier ";
    // 用于分页查询数据库中的所有快递员信息
    private static final String DQL_FIND_LIMIT = " SELECT * FROM ecourier LIMIT ?, ? ";
    private static final String DQL_FIND_BY_ID = " SELECT * FROM ecourier WHERE ID=? ";
    private static final String DQL_FIND_BY_IDCARD = " SELECT * FROM ecourier WHERE IDCARD=? ";
    private static final String DQL_FIND_BY_PHONE = " SELECT * FROM ecourier WHERE PHONE=? ";
    private static final String DML_INSERT = " INSERT INTO ecourier(ID, NAME, PHONE, IDCARD, PASSWORD, DELIVERIES, REGTIME, LOGINTIME, INFO) " +
            " VALUES(NULL,?,?,?,?,0,NOW(),NOW(),?) ";

    private static final String DML_UPDATE = " UPDATE ecourier SET NAME=?,PHONE=?,IDCARD=?,PASSWORD=? WHERE ID=? ";
    private static final String DML_DELETE = " DELETE FROM ecourier WHERE ID=? ";




    /**
     * 用于查询快递员总人数(size)以及日注册量(day_reg)
     *
     * @return
     */
    @Override
    public List<Integer> console() {
        List<Integer> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = DruidUtil.getConnection();
            preparedStatement = connection.prepareStatement(DQL_CONSOLE);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                list.add(resultSet.getInt(1));
                list.add(resultSet.getInt(2));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection, preparedStatement, resultSet);
        }

        return list;
    }

    /**
     * 用于分页查询数据库中的所有快递员信息
     *
     * @param limit      true表示开启分页查询(默认) false 表示查询所有快递员
     * @param offset     表示 SQL 语句起始索引
     * @param pageNumber 表示页查询快递员数量
     * @return List<Courier> 快递员集合
     */
    @Override
    public List<Courier> findAll(boolean limit, int offset, int pageNumber) {
        List<Courier> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = DruidUtil.getConnection();
            if (limit){
                preparedStatement = connection.prepareStatement(DQL_FIND_LIMIT);
                preparedStatement.setInt(1,offset);
                preparedStatement.setInt(2,pageNumber);
            }else {
                preparedStatement = connection.prepareStatement(DQL_FIND_ALL);
            }
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()){
                Courier courier = new Courier();
                courier.setId(resultSet.getInt("id"));
                courier.setName(resultSet.getString("name"));
                courier.setPhone(resultSet.getString("phone"));
                courier.setIdCard(resultSet.getString("idcard"));
                courier.setPassword(resultSet.getString("password"));
                courier.setDeliveries(resultSet.getInt("deliveries"));
                courier.setRegTime(resultSet.getTimestamp("regtime"));
                courier.setLoginTime(resultSet.getTimestamp("logintime"));
                courier.setInfo(resultSet.getString("info"));
                list.add(courier);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection, preparedStatement,resultSet);
        }
        return list;
    }

    /**
     * 根据快递员编号查询快递员信息
     *
     * @param id 快递员编号
     * @return Courier
     */
    @Override
    public Courier findById(int id) {
        Courier courier = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = DruidUtil.getConnection();
            preparedStatement = connection.prepareStatement(DQL_FIND_BY_ID);
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                courier = new Courier();
                courier.setId(resultSet.getInt("id"));
                courier.setName(resultSet.getString("name"));
                courier.setPhone(resultSet.getString("phone"));
                courier.setIdCard(resultSet.getString("idcard"));
                courier.setPassword(resultSet.getString("password"));
                courier.setDeliveries(resultSet.getInt("deliveries"));
                courier.setRegTime(resultSet.getTimestamp("regtime"));
                courier.setLoginTime(resultSet.getTimestamp("logintime"));
                courier.setInfo(resultSet.getString("info"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection, preparedStatement, resultSet);
        }

        return courier;
    }

    /**
     * 根据手机号码查询快递员信息
     *
     * @param phone 手机号码
     * @return Courier
     */
    @Override
    public Courier findByPhone(String phone) {
        Courier courier = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = DruidUtil.getConnection();
            preparedStatement = connection.prepareStatement(DQL_FIND_BY_PHONE);
            preparedStatement.setString(1, phone);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                courier = new Courier();
                courier.setId(resultSet.getInt("id"));
                courier.setName(resultSet.getString("name"));
                courier.setPhone(resultSet.getString("phone"));
                courier.setIdCard(resultSet.getString("idcard"));
                courier.setPassword(resultSet.getString("password"));
                courier.setDeliveries(resultSet.getInt("deliveries"));
                courier.setRegTime(resultSet.getTimestamp("regtime"));
                courier.setLoginTime(resultSet.getTimestamp("logintime"));
                courier.setInfo(resultSet.getString("info"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection, preparedStatement, resultSet);
        }

        return courier;
    }
    /**
     * 根据身份证号码查询快递员信息
     *
     * @param idCard 身份证号码
     * @return Courier
     */
    @Override
    public Courier findByIdCard(String idCard) {
        Courier courier = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = DruidUtil.getConnection();
            preparedStatement = connection.prepareStatement(DQL_FIND_BY_IDCARD);
            preparedStatement.setString(1, idCard);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                courier = new Courier();
                courier.setId(resultSet.getInt("id"));
                courier.setName(resultSet.getString("name"));
                courier.setPhone(resultSet.getString("phone"));
                courier.setIdCard(resultSet.getString("idcard"));
                courier.setPassword(resultSet.getString("password"));
                courier.setDeliveries(resultSet.getInt("deliveries"));
                courier.setRegTime(resultSet.getTimestamp("regtime"));
                courier.setLoginTime(resultSet.getTimestamp("logintime"));
                courier.setInfo(resultSet.getString("info"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection, preparedStatement, resultSet);
        }

        return courier;
    }

    /**
     * 新增快递员
     *
     * @param courier
     * @return
     */
    @Override
    public boolean insert(Courier courier) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;

        int i = 0;
        try {
            connection = DruidUtil.getConnection();
            preparedStatement = connection.prepareStatement(DML_INSERT);
            preparedStatement.setString(1,courier.getName());
            preparedStatement.setString(2,courier.getPhone());
            preparedStatement.setString(3,courier.getIdCard());
            preparedStatement.setString(4,courier.getPassword());
            preparedStatement.setString(5,courier.getInfo());

            i = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection, preparedStatement, null);
        }

        return i>0?true:false;
    }

    /**
     * 根据id修改快递员信息
     *
     * @param id
     * @param newCourier
     * @return
     */
    @Override
    public boolean update(int id, Courier newCourier) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;

        int i = 0;
        try {
            connection = DruidUtil.getConnection();
            preparedStatement = connection.prepareStatement(DML_UPDATE);
            preparedStatement.setString(1,newCourier.getName());
            preparedStatement.setString(2,newCourier.getPhone());
            preparedStatement.setString(3,newCourier.getIdCard());
            preparedStatement.setString(4,newCourier.getPassword());
            preparedStatement.setInt(5,id);

            i = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection, preparedStatement, null);
        }

        return i>0?true:false;

    }

    /**
     * 根据电话号码删除快递员信息
     *
     * @param id
     * @return
     */
    @Override
    public boolean delete(int id) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;

        int i = 0;
        try {
            connection = DruidUtil.getConnection();
            preparedStatement = connection.prepareStatement(DML_DELETE);
            preparedStatement.setInt(1,id);

            i = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection, preparedStatement, null);
        }

        return i>0?true:false;

    }
}
